﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

namespace MMS.DAL
{
    /// <summary>
    /// 创建DBHelper用于多种SQL语句切换使用时不用改写过多代码直接继承重写就好 例如：MySqlDBHelper
    /// </summary>
    public class SqlServerDBHelper : DBHelper
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        private const string DB_CONNECTION_STRING = @"Data Source=.\SQLEXPRESS;Initial Catalog=mmsdb;Integrated Security=True";
        private SqlConnection conn = new SqlConnection(DB_CONNECTION_STRING);

        /// <summary>
        /// 打开数据库
        /// </summary>
        public override void Open()
        {
            conn.Open();

        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        public override void Close()
        {
            conn.Close();
        }

        /// <summary>
        /// 执行一个查询语句，返回受影响的语句  返回的值是INT 类型
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override int ExecuteNonQuery(string sql)
        {
            Open();
            int count = new SqlCommand(sql, conn).ExecuteNonQuery();
            Close();
            return count;
        }
        /// <summary>
        /// 执行查询语句， 返回首行的值 值类型为object类型
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override object ExecuteScalar(string sql)
        {
            Open();
            object data = new SqlCommand(sql, conn).ExecuteScalar();
            Close();
            return data;
        }
        /// <summary>
        /// 执行查询语句， 返回一个 DataReader对象 object 类型
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DbDataReader ExecuteReader(string sql)
        {
            Open();
            return new SqlCommand(sql, conn).ExecuteReader();
            ///DbDataReader操作不需要关闭
        }
        /// <summary>
        /// 创建数据集适配器， 用于将查询到的语句填充到dt
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataTable ExecuteDadaList(string sql)
        {
            DataTable dt = new DataTable();//使用SqlDataAdapter查询并填充数据
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(dt);//填充数据
            return dt;
        }
    }
}
